package cn.edu.nju.software.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.naming.NamingException;
import javax.sql.DataSource;

import cn.edu.nju.software.bean.storage.User;
import cn.edu.nju.software.dao.intf.UserDao;
import cn.edu.nju.software.util.Constants;

public class UserDaoImpl implements UserDao {

	private DataSource ds = null;

	public UserDaoImpl() {
		try {
			ds = JDBCUtil.getDataSource();
		} catch (NamingException e) {
			e.printStackTrace();
		}
	}

	@Override
	public User login(String signInName, String password) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		User user = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("SELECT id, name, email, phone_no, address, "
							+ "status FROM bmuser WHERE act_name = ? AND password = ? "
							+ "AND status <> ?");
			pstmt.setString(1, signInName);
			pstmt.setString(2, password);
			pstmt.setInt(3, Constants.USER_DELETED);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				user = new User(rs.getInt(1), signInName, rs.getString(2),
						password, rs.getString(3), rs.getString(4),
						rs.getString(5), rs.getInt(6));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return user;
	}

	@Override
	public int registerUser(User user) throws SQLException {
		int result = -1;
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(
					"INSERT INTO bmuser (act_name, name, password, email, "
							+ "phone_no, address) VALUES (?, ?, ?, ?, ?, ?)",
					Statement.RETURN_GENERATED_KEYS);
			pstmt.setString(1, user.getSignInName());
			pstmt.setString(2, user.getName());
			pstmt.setString(3, user.getPassword());
			pstmt.setString(4, user.getEmail());
			pstmt.setString(5, user.getPhone());
			pstmt.setString(6, user.getAddress());
			pstmt.executeUpdate();
			rs = pstmt.getGeneratedKeys();
			while (rs.next()) {
				result = rs.getInt(1);
			}
			con.commit();
		} catch (SQLException e) {
			int errorCode = e.getErrorCode();
			con.rollback();
			if (errorCode == JDBCUtil.VIOLATION_OF_PK_CONSTRAINT) {
				result = -1;
			} else {
				e.printStackTrace();
				throw e;
			}
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return result;
	}

	@Override
	public void updateUser(User user) throws SQLException {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("UPDATE bmuser SET name = ?, password = ?, "
							+ "email = ?, phone_no = ?, address = ? WHERE id = ?");
			pstmt.setString(1, user.getName());
			pstmt.setString(2, user.getPassword());
			pstmt.setString(3, user.getEmail());
			pstmt.setString(4, user.getPassword());
			pstmt.setString(5, user.getAddress());
			pstmt.setInt(6, user.getUserID());
			pstmt.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
	}

	@Override
	public void blockUser(String signInName) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("UPDATE bmuser SET status = ? WHERE act_name = ?");
			pstmt.setInt(1, Constants.USER_BLOCKED);
			pstmt.setString(2, signInName);
			pstmt.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
	}

	@Override
	public void unblockUser(String signInName) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("UPDATE bmuser SET status = ? WHERE act_name = ?");
			pstmt.setInt(1, Constants.USER_NORMAL);
			pstmt.setString(2, signInName);
			pstmt.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
	}

	@Override
	public void deleteUser(String signInName) throws SQLException {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con
					.prepareStatement("UPDATE bmuser SET status = ? WHERE act_name = ?");
			pstmt.setInt(1, Constants.USER_DELETED);
			pstmt.setString(2, signInName);
			pstmt.executeUpdate();
			con.setAutoCommit(false);
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
	}

	@Override
	public Map<Integer, User> getUsersByID(List<Integer> idList)
			throws SQLException {
		// TODO Auto-generated method stub
		Map<Integer, User> map = new HashMap<Integer, User>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			StringBuilder sql = new StringBuilder(
					"SELECT id, act_name, name, password, "
							+ "email, phone_no, address, status FROM bmuser WHERE id IN (");
			int size = idList.size();
			for (int i = 0; i < size - 1; ++i) {
				sql.append("?, ");
			}
			sql.append("?)");
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement(sql.toString());
			int count = 1;
			for (int i : idList) {
				pstmt.setInt(count, i);
				++count;
			}
			rs = pstmt.executeQuery();
			while (rs.next()) {
				map.put(rs.getInt(1),
						new User(rs.getInt(1), rs.getString(2),
								rs.getString(3), rs.getString(4), rs
										.getString(5), rs.getString(6), rs
										.getString(7), rs.getInt(8)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return map;
	}

	@Override
	public List<User> getUsersBySignInName(String signInName)
			throws SQLException {
		// TODO Auto-generated method stub
		List<User> list = new ArrayList<User>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("SELECT id, act_name, name, "
					+ "password, email, phone_no, address, status FROM bmuser "
					+ "WHERE act_name LIKE '%?%'");
			pstmt.setString(1, signInName);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new User(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getString(6), rs.getString(7), rs.getInt(8)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

	@Override
	public List<User> getUsersByName(String name) throws SQLException {
		// TODO Auto-generated method stub
		List<User> list = new ArrayList<User>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("SELECT id, act_name, name, "
					+ "password, email, phone_no, address, status FROM bmuser "
					+ "WHERE name LIKE '%?%'");
			pstmt.setString(1, name);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new User(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getString(6), rs.getString(7), rs.getInt(8)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}

	@Override
	public List<User> getAllUsers() throws SQLException {
		// TODO Auto-generated method stub
		List<User> list =new ArrayList<User>();
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			con = ds.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("SELECT id, act_name, name, password, email, phone_no, "
					+ "address, status FROM bmuser");
			rs = pstmt.executeQuery();
			while (rs.next()) {
				list.add(new User(rs.getInt(1), rs.getString(2), rs
						.getString(3), rs.getString(4), rs.getString(5), rs
						.getString(6), rs.getString(7), rs.getInt(8)));
			}
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			con.rollback();
			throw e;
		} finally {
			JDBCUtil.closeResultSet(rs);
			JDBCUtil.closeStatement(pstmt);
			JDBCUtil.closeConnection(con);
		}
		return list;
	}
}
